﻿using System;
using System.Collections.Generic;
using System.Text;
using Windvoice.Blog.Model;
using System.Configuration;
using System.Data;
using Windvoice.Util;

namespace Windvoice.Blog.DataAccess
{
    public class CategoryDAL
    {
        private string ConnectionString = ConfigurationManager.ConnectionStrings["windvoice"].ToString();

        public int Category_Insert(CategoryInfo category)
        {
            string f = "INSERT INTO [Category] ([nameen],[descriptionen],[namecn],[descriptioncn]) VALUES ('{0}','{1}','{2}','{3}')";
            string command = String.Format(f,category.NameEN,category.DescriptionEN,category.NameCN,category.DescriptionCN);
            return AccessHelper.ExecuteNonQuery(ConnectionString,command); 
        }

        public int Category_Delete(int CategoryID)
        {
            string command = "DELETE FROM [Category] WHERE [id]="+CategoryID.ToString();
            return AccessHelper.ExecuteNonQuery(ConnectionString,command); 
        }

        public int Category_Update(CategoryInfo category)
        {
            string f = "UPDATE [Category] SET [nameen]='{0}',[descriptionen]='{1}',[namecn]='{2}',[descriptioncn]='{3}' WHERE [id]={4}";
            string command = String.Format(f,category.NameEN,category.DescriptionEN,category.NameCN,category.DescriptionCN,category.ID);
            return AccessHelper.ExecuteNonQuery(ConnectionString,command);
        }

        public DataTable Category_GetList()
        {
            string command = "SELECT * FROM [Category]";
            return AccessHelper.ExecuteDataSet(ConnectionString,command).Tables[0]; 
        }

        public CategoryInfo Category_Get(int CategoryID)
        {
            string command = "SELECT * FROM [Category] WHERE [id]="+CategoryID.ToString();

            DataTable dt = AccessHelper.ExecuteDataSet(ConnectionString,command).Tables[0]; 

            CategoryInfo category=null;

            if (dt.Rows.Count > 0) category = new CategoryInfo(dt.Rows[0]);

            return category; 
        }

        public int Category_GetArticleCount(int CategoryID)
        {
            string command = String.Empty;
            if (CategoryID == 0) command = "SELECT COUNT(0) FROM [article]";
            else command = "SELECT COUNT(0) FROM [article] WHERE [categoryid]=" + CategoryID.ToString();
            return (int)AccessHelper.ExecuteScaler(ConnectionString,command);
        }
    }
}
